# _*_ coding:utf-8 _*_
# @File  : futures_daily_receipt.py
# @Time  : 2021-09-27  13:36
# @Author: zizle
import json
import os
import re
import datetime

import requests
import numpy as np
import pandas as pd
from hutool import utils as hu_tool
from utils.constant import VARIETY_EN
from logger import logger


class CZCEDailyReceipt(object):
    def __init__(self, receipt_date: datetime.datetime):
        self.temp_folder = 'temp/czce/receipt'
        self.parse_day = receipt_date.strftime("%Y%m%d")
        self.xls_url = "http://www.czce.com.cn/cn/DFSStaticFiles/Future/{}/{}/FutureDataWhsheet.xls".format(receipt_date.year, self.parse_day)
        self.headers = {
            'User-Agent': 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36',
            'Host': 'www.czce.com.cn',
        }
        self.filepath = f'{self.temp_folder}/{self.parse_day}.xls'
        if not os.path.exists(self.temp_folder):
            os.makedirs(self.temp_folder)

    def get_receipt_xls_file(self):
        try:
            r = requests.get(self.xls_url, headers=self.headers)
            content = r.content
            if r.status_code == 200:
                with open(self.filepath, 'wb') as f:
                    f.write(content)
            else:
                if os.path.exists(self.filepath):
                    os.remove(self.filepath)
        except Exception as e:
            logger.error(f'Spider {self.parse_day} CZCE daily-receipt Error: {e}')
            return False
        else:
            logger.info(f'Spider {self.parse_day} CZCE daily-receipt Successfully!')
            return True

    def get_daily_receipt(self):
        if not os.path.exists(self.filepath):
            ret = self.get_receipt_xls_file()
            if not ret:
                return [], 'Spider File Fail!'
        # 执行文件分析
        xls_df = pd.read_excel(self.filepath)
        xls_df = xls_df.fillna('')
        variety_index_dict = dict()
        variety_dict = dict()
        variety_en = None  # 品种标记
        pre_variety_en = None
        for row_content in xls_df.itertuples():
            info_for_match_ = hu_tool.full_width_to_half_width(row_content[1])
            search_v = re.search(r'品种:(.*)\s单位.*', info_for_match_)  # 品种
            total_count = re.search(r'总计', info_for_match_)
            if search_v:  # 取得品种和品种的英文代码
                pre_variety_en = variety_en
                has_new_variety = True
                zh_en_variety = search_v.group(1)
                variety_name, variety_en = hu_tool.split_zh_en(zh_en_variety)
                if variety_en == "PTA":
                    variety_en = "TA"
                variety_dict[variety_en] = variety_name
                variety_index_dict[variety_en] = [row_content[0] + 1]
            else:
                has_new_variety = False
            # 获取当前品种的数据表
            if total_count and variety_en:
                variety_index_dict[variety_en].append(row_content[0])
            # 当没有总计时有上一个品种记录且找到了新品种，那么老品种结束行应该是找到新品种行的上一行
            if not total_count and pre_variety_en and has_new_variety:  # 补充没有总计时无法添加结束行的问题，该问题与20191111日后的数据出现
                variety_index_dict[pre_variety_en].append(row_content[0] - 1)

        # 整理数据
        column_indexes = ["warehouse", "variety_en", "receipt", "increase"]

        result_df = pd.DataFrame(columns=column_indexes)
        for variety_en in variety_dict:
            data_index_range = variety_index_dict[variety_en]  # 数据在dataFrame中的起终索引
            variety_df = xls_df.iloc[data_index_range[0]:data_index_range[1] + 1, :]
            variety_df = self._parser_receipt_sub_df(variety_en, variety_df)
            result_df = pd.concat([result_df, variety_df])
        # 增加ex_total字段
        result_df['ex_total'] = [0 for _ in range(result_df.shape[0])]
        return result_df.to_dict(orient='record'), 'Parser CZCE Receipt Successfully!'

    @staticmethod
    def _parser_receipt_sub_df(variety_en, variety_df):
        """ 解析郑商所每个品种的仓单日报 """
        # print(variety_df)
        # 20200220后的数据强筋小麦为机构简称和机构编号，仓单为‘确认书数量’
        variety_df.columns = variety_df.iloc[0].replace('机构编号', '仓库编号').replace('机构简称', '仓库简称').replace(
            '厂库编号', '仓库编号').replace('厂库简称', '仓库简称').replace('仓单数量(完税)', '仓单数量').replace('确认书数量', '仓单数量')  # 以第一行为列头
        variety_df = variety_df.drop(variety_df.index[0])  # 删除第一行
        variety_df = variety_df[~variety_df['仓库编号'].str.contains('总计|小计')]  # 选取不含有小计和总计的行
        # 把仓库简称的列空置替换为NAN，并使用前一个进行填充
        variety_df['仓库编号'] = variety_df['仓库编号'].replace('', np.nan).fillna(method='ffill')
        variety_df['仓库简称'] = variety_df['仓库简称'].replace('', np.nan).fillna(method='ffill')
        variety_df['仓单数量'] = variety_df['仓单数量'].replace('', np.nan).fillna(0)

        # 目标数据样式
        # 代码      仓库      仓单      增减
        # CF        河南国储   20       0

        # if '升贴水' not in variety_df.columns:
        #     variety_df['升贴水'] = [0 for _ in range(variety_df.shape[0])]
        # variety_df['升贴水'] = variety_df['升贴水'].replace('-', 0)

        # 将仓单数量列转为float
        variety_df['仓单数量'] = variety_df['仓单数量'].apply(float)  # 转为int计算
        variety_df['当日增减'] = variety_df['当日增减'].apply(float)
        result_df = pd.DataFrame()
        result_df['仓单数量'] = variety_df['仓单数量'].groupby(variety_df['仓库简称']).sum()  # 计算和
        result_df['当日增减'] = variety_df['当日增减'].groupby(variety_df['仓库简称']).sum()
        result_df.reset_index()
        wh_name = variety_df[['仓库简称']].drop_duplicates(subset='仓库简称', keep='first')
        result_df = pd.merge(wh_name, result_df, on='仓库简称')
        result_df['品种代码'] = [variety_en for _ in range(result_df.shape[0])]
        # 将仓库简称的字符转为半角
        result_df["仓库简称"] = result_df["仓库简称"].apply(hu_tool.full_width_to_half_width)
        result_df = result_df.reindex(columns=["仓库简称", "品种代码", "仓单数量", "当日增减"])
        result_df.columns = ["warehouse", "variety_en", "receipt", "increase"]
        return result_df


class DCEDailyReceipt(object):
    def __init__(self, receipt_date: datetime.datetime):
        self.temp_folder = 'temp/dce/receipt'
        self.parse_day = receipt_date.strftime("%Y%m%d")
        self.html_url = 'http://www.dce.com.cn/publicweb/quotesdata/wbillWeeklyQuotes.html'
        self.form_data = {
            'wbillWeeklyQuotes.variety': 'all',
            'year': str(receipt_date.year),
            'month': str(receipt_date.month - 1),
            'day': receipt_date.strftime("%d"),
        }
        self.headers = {
            'User-Agent': 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36',
            'Host': 'www.dce.com.cn',
            'Content-Type': 'application/x-www-form-urlencoded',
        }

        self.filepath = f'{self.temp_folder}/{self.parse_day}.html'
        if not os.path.exists(self.temp_folder):
            os.makedirs(self.temp_folder)

    def get_receipt_file(self):
        try:
            r = requests.post(url=self.html_url, data=self.form_data, headers=self.headers)
            content = r.content
            if r.status_code == 200:
                with open(self.filepath, 'wb') as f:
                    f.write(content)
            else:
                if os.path.exists(self.filepath):
                    os.remove(self.filepath)
        except Exception as e:
            logger.error(f'Spider {self.parse_day} DCE daily-receipt Error: {e}')
            return False
        else:
            logger.info(f'Spider {self.parse_day} DCE daily-receipt Successfully!')
            return True

    def get_daily_receipt(self):
        if not os.path.exists(self.filepath):
            ret = self.get_receipt_file()
            if not ret:
                return [], 'Spider File Fail!'
        # 解析文件
        html_df = pd.read_html(self.filepath, encoding='utf-8')[0]
        if html_df.columns.values.tolist() != ['品种', '仓库/分库', '昨日仓单量', '今日仓单量', '增减']:
            logger.error("郑商所的仓单数据格式出现错误,解析失败!")
            return [], 'Parse Fail, File Error!'
        # 修改列头
        html_df.columns = ["VARIETY", "WARENAME", "YRECEIPT", "TRECEIPT", "INCREATE"]

        # 增加一行标记，VARIETY=nan时，不计入总计的标记
        html_df['EX_TOTAL'] = html_df['VARIETY'].apply(lambda x: 1 if pd.isnull(x) else 0)

        # 填充nan为上一行数据
        html_df.fillna(method='ffill', inplace=True)
        # 去除品种含小计,总计等行
        html_df = html_df[~html_df['VARIETY'].str.contains('总计|小计|合计')]
        # # 选取含有小计的行(20210226改：取品种的结果只能取这个才准确)
        # html_df = html_df[html_df['VARIETY'].str.contains('小计')]
        # 仓库简称处理
        html_df["WARENAME"] = html_df["WARENAME"].apply(hu_tool.full_width_to_half_width)
        # 增加品种代码
        html_df["VARIETYEN"] = html_df["VARIETY"].apply(self.get_variety_en)
        # 重置索引
        result_df = html_df.reindex(columns=["WARENAME", "VARIETYEN", "TRECEIPT", "INCREATE", 'EX_TOTAL'])
        result_df.columns = ["warehouse", "variety_en", "receipt", "increase", 'ex_total']

        # # 品种分组求和
        # sum_df = result_df.groupby(by=['VARIETYEN'], as_index=False)[['TRECEIPT', 'INCREATE']].sum()
        # sum_df.columns = ["variety_en", "receipt", "increase"]
        # sum_df = sum_df.reindex(columns=["variety_en", "receipt", "increase"])
        return result_df.to_dict(orient='records'), 'Spider Successfully'

    @staticmethod
    def get_variety_en(name):
        variety_en = VARIETY_EN.get(name.replace('小计', '').replace(' ', ''), None)
        if variety_en is None:
            logger.error("大商所{}品种对应的代码不存在...".format(name))
            raise ValueError("品种不存在...")
        return variety_en


class SHFEDailyReceipt(object):
    def __init__(self, receipt_date):
        self.temp_folder = 'temp/shfe/receipt'
        self.parse_day = receipt_date.strftime("%Y%m%d")
        self.receipt_url = "http://www.shfe.com.cn/data/dailydata/{}dailystock.dat".format(self.parse_day)
        self.headers = {
            'User-Agent': 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36',
            'Host': 'www.shfe.com.cn',
        }
        self.filepath = f'{self.temp_folder}/{self.parse_day}.json'
        if not os.path.exists(self.temp_folder):
            os.makedirs(self.temp_folder)

    def get_receipt_file(self):
        try:
            r = requests.get(self.receipt_url, headers=self.headers)
            content = r.json()
            with open(self.filepath, "w", encoding="utf-8") as json_file:
                json.dump(content, json_file, indent=4, ensure_ascii=False)
        except Exception as e:
            logger.error(f'Spider {self.parse_day} SHFE daily-receipt Error: {e}')
            return False
        else:
            logger.info(f'Spider {self.parse_day} SHFE daily-receipt Successfully!')
            return True

    def get_daily_receipt(self):
        if not os.path.exists(self.filepath):
            ret = self.get_receipt_file()
            if not ret:
                return [], 'Spider SHFE File Fail!'
        with open(self.filepath, "r", encoding="utf-8") as reader:
            source_content = json.load(reader)
        json_df = pd.DataFrame(source_content['o_cursor'])
        # 处理仓库名称
        json_df["WHABBRNAME"] = json_df["WHABBRNAME"].apply(hu_tool.full_width_to_half_width)
        json_df["WHABBRNAME"] = json_df["WHABBRNAME"].apply(lambda name: name.split("$$")[0].strip())
        # 去掉含仓库名称含合计或小计的行
        json_df = json_df[~json_df['WHABBRNAME'].str.contains('总计|小计|合计')]  # 选取品种不含有小计和总计合计的行
        # 处理品种名称
        json_df["VARNAME"] = json_df["VARNAME"].apply(lambda name: name.split("$$")[0].strip())
        # 增加交易代码列
        json_df["VAREN"] = json_df['VARNAME'].apply(self.get_variety_en)
        # 仓单和增减转为int类型
        json_df["WRTWGHTS"] = json_df["WRTWGHTS"].apply(int)
        json_df["WRTCHANGE"] = json_df["WRTCHANGE"].apply(int)
        # 整理出想要的数据列(仓库名称(简称),交易代码,仓单,增减)
        result_df = json_df.reindex(columns=["WHABBRNAME", "VAREN", "WRTWGHTS", "WRTCHANGE"])
        result_df.columns = ["warehouse", "variety_en", "receipt", "increase"]
        # print(result_df)
        # # 以品种分组求和
        # sum_df = result_df.groupby(by=['variety_en'], as_index=False)[['receipt', 'increase']].sum()
        # sum_df.columns = ["variety_en", "receipt", "increase"]
        # sum_df = sum_df.reindex(columns=["variety_en", "receipt", "increase"])
        # 增加ex_total字段
        result_df['ex_total'] = [0 for _ in range(result_df.shape[0])]
        return result_df.to_dict(orient='records'), 'Spider SHFE File Successfully!'

    @staticmethod
    def get_variety_en(name):
        variety_en = VARIETY_EN.get(name.replace('小计', '').replace(' ', ''), None)
        if variety_en is None:
            logger.error("大商所{}品种对应的代码不存在...".format(name))
            raise ValueError("品种不存在...")
        return variety_en



